﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NPOI.HSSF.Record;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using Test.Entity;

namespace Test.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class UserController : ControllerBase
    {
        [HttpGet("AllUserList")]
        public List<AccountInfo> AllUserList()
        {
            var res = SqlSugarHelper.Db().Queryable<AccountInfo>().ToList();
            SqlSugarHelper.Db().Insertable(res);
            return res;
        }


        [HttpPost("uploadExcel")]
        public async Task<IActionResult> uploadExcel(IFormFile  file)
        {
            var message = "";
            long id = 0;
            List<UploadExcelInfo> uploadList = new List<UploadExcelInfo>();
            var fileName = file.FileName;
            try
            {
                //if (file.Length <= 0)
                //{
                //    return BadRequest("未上传文件!");
                //}

                // 快速格式化  ctrl + k , +d
                var data = new UploadExcel
                {
                    CreateDate = DateTime.Now,
                    ExcelName = fileName
                };
                id = SqlSugarHelper.Db().Insertable<UploadExcel>(data).ExecuteReturnSnowflakeId();
                using (var stream = file.OpenReadStream())
                {
                    IWorkbook workbook;

                    // 根据扩展名判断 Excel 文件类型
                    if (Path.GetExtension(file.FileName).Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(stream);  // 处理 .xls 文件
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(stream);  // 处理 .xlsx 文件
                    }
                    ISheet sheet = workbook.GetSheetAt(0);  // 获取第一个工作表

                    // 假设第一行是标题，从第二行开始读取数据
                    for (int row = 1; row <= sheet.LastRowNum; row++)
                    {
                        IRow excelRow = sheet.GetRow(row);
                        if (excelRow != null)
                        {
                            int.TryParse(excelRow.GetCell(1)?.ToString(),out int tempAge);
                            var rowData = new UploadExcelInfo
                            {
                                UserName = excelRow.GetCell(0)?.ToString(),  // 第一列数据
                                Age = tempAge,  // 第二列数据
                                Sex = excelRow.GetCell(2)?.ToString(),   // 第三列数据
                                UploadExcelId = id
                            };
                            uploadList.Add(rowData);
                        }
                    }
                }
                SqlSugarHelper.Db().Insertable<UploadExcelInfo>(uploadList).ExecuteCommand();
            } catch (Exception ex) {
                message = ex.Message;
            }

            return Ok(new
            {
                message,
                id,
                uploadList
            });
        }

        [HttpPost("uploadExcelSave")]
        public async Task<IActionResult> uploadExcelSave(IFormFile file)
        {
            var message = "";
            long id = 0;
            List<UploadExcelInfo> uploadList = new List<UploadExcelInfo>();
            var fileName = file.FileName;
            var desktopPath = "";
            try
            {
                //if (file.Length <= 0)
                //{
                //    return BadRequest("未上传文件!");
                //}

                // 快速格式化  ctrl + k , +d
                var data = new UploadExcel
                {
                    CreateDate = DateTime.Now,
                    ExcelName = fileName
                };
                id = SqlSugarHelper.Db().Insertable<UploadExcel>(data).ExecuteReturnSnowflakeId();
                using (var stream = file.OpenReadStream())
                {
                    IWorkbook workbook;

                    // 根据扩展名判断 Excel 文件类型
                    if (Path.GetExtension(file.FileName).Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(stream);  // 处理 .xls 文件
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(stream);  // 处理 .xlsx 文件
                    }
                    ISheet sheet = workbook.GetSheetAt(0);  // 获取第一个工作表

                    int totalAge = 0;  // 汇总年龄
                    int personCount = 0;  // 人员总数

                    // 假设第一行是标题，从第二行开始读取数据
                    for (int row = 1; row <= sheet.LastRowNum; row++)
                    {
                        IRow excelRow = sheet.GetRow(row);
                        if (excelRow != null)
                        {

                            int.TryParse(excelRow.GetCell(1)?.ToString(), out int tempAge);

                            totalAge += tempAge;  // 累加年龄
                            personCount++;  // 计算人员数量

                    
                            var rowData = new UploadExcelInfo
                            {
                                UserName = excelRow.GetCell(0)?.ToString(),  // 第一列数据
                                Age = tempAge,  // 第二列数据
                                Sex = excelRow.GetCell(2)?.ToString(),   // 第三列数据
                                UploadExcelId = id
                            };
                            uploadList.Add(rowData);
                        }
                    }

                    // Step 4: 将汇总信息插入到 Excel 的最后一行，不插入数据库
                    IRow summaryRow = sheet.CreateRow(sheet.LastRowNum + 1);
                     // summaryRow.CreateCell(0).SetCellValue("汇总");
                    summaryRow.CreateCell(1).SetCellValue(personCount);  // 人员总数
                    summaryRow.CreateCell(2).SetCellValue(totalAge);  // 年龄总和
                    

                    // Step 5: 保存 Excel 文件到桌面
                     desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                    var savedFileName = Path.Combine(desktopPath, $"汇总_{fileName}");

                    using (var fileStream = new FileStream(savedFileName, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fileStream);
                    }
                }
                SqlSugarHelper.Db().Insertable<UploadExcelInfo>(uploadList).ExecuteCommand();
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }

            return Ok(new
            {
                message,
                id,
                uploadList,
                desktopPath
            });
        }


        [HttpPost]
        public string Comoputed ()
        {
            var str = "";
            return str;
        }
    }
}
